import xlwt
# import copy
# import json
# import base64
# from statistics import mean
from datetime import datetime, timedelta
# from zoneinfo import ZoneInfo
from collections import defaultdict
from pytz import timezone, utc
from itertools import groupby

from odoo import http, _
from odoo.http import request, content_disposition
# from odoo.tools.misc import format_datetime
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT


class ReportAPI(http.Controller):
    def create_style(self, font_height, center=True, borders=True, bold=False):
        style = xlwt.XFStyle()
        font = xlwt.Font()
        font.height = font_height
        font.bold = bold
        style.font = font
        if center:
            # 垂直居中 水平居中
            vertically_horizontally_centered = xlwt.Alignment()
            vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
            vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
            style.alignment = vertically_horizontally_centered
        if borders:
            border = xlwt.Borders()
            border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
            style.borders = border
        return style

    def get_query(self, from_date, to_date, category_ids, default_code=False, lot_ids=False):
        query = f"""
                WITH
                -- 库存位置
                location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
                location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
                location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
                -- 生产前位置
                location4 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal')),
                location5 AS (SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal'),
                location6 AS (SELECT l4.id FROM location4 l4 UNION ALL SELECT l5.id FROM location5 l5 ORDER BY id asc),
                product AS (
        			SELECT pt.name, pp.id, pp.default_code, pt.categ_id, pc.name as category_name
        			FROM product_product pp
        			    LEFT JOIN product_template pt ON pp.product_tmpl_id=pt.id
        			    LEFT JOIN product_category pc on pt.categ_id=pc.id
        			WHERE
        			    pp.default_code IS NOT NULL and
        			    pc.id not in {tuple(category_ids)}
        		),
                move_inout AS (
                    -- 入库
                    SELECT
                        sm.reference,
                        sml.id,
                        sml.picking_id,
                        sm.id as move_id,
                        sml.product_id,
                        sl1.name as location_name,
                        sl2.name as location_dest_name,
                        sml.date,
                        sml.lot_id,
                        sml.product_uom_id,
                        sml.quantity,
                        'in' AS type,
                        CASE
                            -- 出库
                            WHEN sl1.usage='internal' AND sl2.usage='supplier' THEN '采购退货'
                            WHEN sl1.usage='internal' AND sl2.usage='customer' THEN '销售出库'
                            WHEN sl1.usage='internal' AND sl2.id in (select id from location6) THEN '生产领料'
                            WHEN sl1.usage='internal' AND sl2.usage='inventory' THEN '物料申领'
                            -- 入库
                            WHEN sl1.usage='supplier' AND sl2.usage='internal' THEN '采购入库'
                            WHEN sl1.usage='customer' AND sl2.usage='internal' THEN '销售退货入库'
                            WHEN sl1.usage='inventory' AND sl2.usage='internal' THEN '物料申领退回'
                            WHEN sl1.id in (select id from location6) AND sl2.usage='internal' THEN '物料申领退回'
                        ELSE NULL END AS bs_type
                    FROM stock_move_line sml
                        left join stock_move sm ON sml.move_id=sm.id
                        LEFT JOIN stock_location sl1 ON sml.location_id=sl1.id
                        LEFT JOIN stock_location sl2 ON sml.location_dest_id=sl2.id
                    WHERE
                        sml.state='done' AND
                        sml.date>='{from_date}' AND
                        sml.date<='{to_date}' AND
                        sml.location_dest_id IN (SELECT id FROM location3) AND
                        sml.location_id not in (SELECT id FROM location3)
                    UNION ALL 
                    --出库
                    SELECT
                        sm.reference,
                        sml.id,
                        sml.picking_id,
                        sm.id as move_id,
                        sml.product_id,
                        sl1.name as location_name,
                        sl2.name as location_dest_name,
                        sml.date,
                        sml.lot_id,
                        sml.product_uom_id,
                        -sml.quantity AS quantity,
                        'out' AS type,
                        CASE
                            -- 出库
                            WHEN sl1.usage='internal' AND sl2.usage='supplier' THEN '采购退货'
                            WHEN sl1.usage='internal' AND sl2.usage='customer' THEN '销售出库'
                            WHEN sl1.usage='internal' AND sl2.id in (select id from location6) THEN '生产领料'
                            WHEN sl1.usage='internal' AND sl2.usage='inventory' THEN '物料申领'
                            -- 入库
                            WHEN sl1.usage='supplier' AND sl2.usage='internal' THEN '采购入库'
                            WHEN sl1.usage='customer' AND sl2.usage='internal' THEN '销售退货入库'
                            WHEN sl1.usage='inventory' AND sl2.usage='internal' THEN '物料申领退回'
                            WHEN sl1.id in (select id from location6) AND sl2.usage='internal' THEN '物料申领退回'
                        ELSE NULL END AS bs_type
                    FROM stock_move_line sml
                        left join stock_move sm ON sml.move_id=sm.id
                        LEFT JOIN stock_location sl1 ON sml.location_id=sl1.id
                        LEFT JOIN stock_location sl2 ON sml.location_dest_id=sl2.id
                    WHERE
                        sml.state='done' AND
                        sml.date>='{from_date}' AND
                        sml.date<='{to_date}' AND
                        sml.location_id IN (SELECT id FROM location3) AND
                        sml.location_dest_id not in (SELECT id FROM location3)
                    )
                    SELECT 
                        mio.id AS move_line_id,
                        mio.picking_id,
                        mio.move_id,
                        mio.reference,
                        pp.id as product_id,
                        pp.name as product_name,
                        pp.default_code,
                        mio.location_name,
                        mio.location_dest_name,
                        mio.date,
                        sl.name as lot_name,
                        sl.id as lot_id,
                        uu.name ->>'zh_CN' as uom_name,
                        uu.id as uom_id,
                        mio.quantity,
                        mio.type,
        				mio.bs_type
                    FROM move_inout mio
                        LEFT JOIN product pp ON mio.product_id=pp.id
                        LEFT JOIN stock_lot sl on sl.id=mio.lot_id
                        LEFT JOIN uom_uom uu on uu.id=mio.product_uom_id
                    WHERE pp.categ_id NOT IN {tuple(category_ids)}
                """
        if default_code:
            if len(lot_ids) > 1:
                query += "and pp.default_code='%s' and sl.id in %s" % (default_code, tuple(lot_ids))
            else:
                query += "and pp.default_code='%s' and sl.id = '%s'" % (default_code, lot_ids[0])
        return query

    def get_production_query(self, from_date, to_date, category_ids, default_code, lot_ids):
        query = f"""
    WITH
    location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal')),
    location2 AS (SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal'),
    location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
    product AS (
        SELECT pt.name, pp.id, pp.default_code, pt.categ_id, pc.name as category_name
        FROM product_product pp
            LEFT JOIN product_template pt ON pp.product_tmpl_id=pt.id
            LEFT JOIN product_category pc on pt.categ_id=pc.id
        WHERE
            pp.default_code IS NOT NULL and
            pc.id in {tuple(category_ids)}
    ),
    move_inout AS (
        -- 入库
        SELECT
            sm.reference,
            sml.id,
            sml.picking_id,
            sm.id as move_id,
            sml.product_id,
            sl1.name as location_name,
            sl2.name as location_dest_name,
            sml.date,
            sml.lot_id,
            sml.product_uom_id,
            sml.quantity,
            'in' AS type,
            CASE
                -- 出库
                WHEN sl1.usage='internal' AND sl2.usage='supplier' THEN '采购退货'
                WHEN sl1.usage='internal' AND sl2.usage='customer' THEN '销售出库'
                WHEN sl1.usage='internal' AND sl2.usage='inventory' THEN '物料申领'
                -- 入库
                WHEN sl1.usage='supplier' AND sl2.usage='internal' THEN '采购入库'
                WHEN sl1.usage='customer' AND sl2.usage='internal' THEN '销售退货入库'
                WHEN sl1.usage='inventory' AND sl2.usage='internal' THEN '物料申领退回'
            ELSE NULL END AS bs_type
        FROM stock_move_line sml
            left join stock_move sm ON sml.move_id=sm.id
            LEFT JOIN stock_location sl1 ON sml.location_id=sl1.id
            LEFT JOIN stock_location sl2 ON sml.location_dest_id=sl2.id
        WHERE
            sml.state='done' AND
            sml.date>='{from_date}' AND
            sml.date<='{to_date}' AND
            sml.location_dest_id IN (SELECT id FROM location3) AND
            sml.location_id not in (SELECT id FROM location3)
        UNION ALL 
        --出库
        SELECT
            sm.reference,
            sml.id,
            sml.picking_id,
            sm.id as move_id,
            sml.product_id,
            sl1.name as location_name,
            sl2.name as location_dest_name,
            sml.date,
            sml.lot_id,
            sml.product_uom_id,
            -sml.quantity AS quantity,
            'out' AS type,
            CASE
                -- 出库
                WHEN sl1.usage='internal' AND sl2.usage='supplier' THEN '采购退货'
                WHEN sl1.usage='internal' AND sl2.usage='customer' THEN '销售出库'
                WHEN sl1.usage='internal' AND sl2.usage='inventory' THEN '物料申领'
                -- 入库
                WHEN sl1.usage='supplier' AND sl2.usage='internal' THEN '采购入库'
                WHEN sl1.usage='customer' AND sl2.usage='internal' THEN '销售退货入库'
                WHEN sl1.usage='inventory' AND sl2.usage='internal' THEN '物料申领退回'
            ELSE NULL END AS bs_type
        FROM stock_move_line sml
            left join stock_move sm ON sml.move_id=sm.id
            LEFT JOIN stock_location sl1 ON sml.location_id=sl1.id
            LEFT JOIN stock_location sl2 ON sml.location_dest_id=sl2.id
        WHERE
            sml.state='done' AND
            sml.date>='{from_date}' AND
            sml.date<='{to_date}' AND
            sml.location_id IN (SELECT id FROM location3) AND
            sml.location_dest_id not in (SELECT id FROM location3)
        )
        SELECT 
            mio.id AS move_line_id,
            mio.picking_id,
            mio.move_id,
            mio.reference,
            pp.id as product_id,
            pp.name as product_name,
            pp.default_code,
            mio.location_name,
            mio.location_dest_name,
            mio.date,
            sl.name as lot_name,
            sl.id as lot_id,
            uu.name ->>'zh_CN' as uom_name,
            uu.id as uom_id,
            mio.quantity,
            mio.type,
            mio.bs_type
        FROM move_inout mio
            LEFT JOIN product pp ON mio.product_id=pp.id
            LEFT JOIN stock_lot sl on sl.id=mio.lot_id
            LEFT JOIN uom_uom uu on uu.id=mio.product_uom_id
        WHERE pp.categ_id IN {tuple(category_ids)}
                        """
        if default_code:
            if len(lot_ids) > 1:
                query += "and pp.default_code='%s' and sl.id in %s" % (default_code, tuple(lot_ids))
            else:
                query += "and pp.default_code='%s' and sl.id = '%s'" % (default_code, lot_ids[0])
        return query

    @http.route('/download/sb/product/quant/excel', type='http', methods=['get'])
    def product_post(self, **kwargs):
        from_date = kwargs.get("from_date")
        to_date = kwargs.get("to_date")
        sb_type = kwargs.get("tp")
        book = xlwt.Workbook(encoding='utf-8')
        header_field_style = self.create_style(15 * 15, bold=True)
        field_style = self.create_style(15 * 15)
        stock_move = request.env['stock.move'].sudo()
        stock_move_line = request.env['stock.move.line'].sudo()
        stock_lot = request.env['stock.lot'].sudo()
        stock_picking = request.env['stock.picking'].sudo()
        uom = request.env['uom.uom'].sudo()
        c = request.env['product.category'].sudo().search([('code', '=', 'OPFP')])
        c |= c.child_id
        category_ids = [request.env.ref('sb_sale.semi_product_type').id,
                        request.env.ref('sb_sale.finished_product_type').id]
        category_ids.extend(c.ids)
        if sb_type == 'detail':
            query = self.get_query(from_date, to_date, category_ids)
            request.env.cr.execute(query)
            query_result = request.env.cr.dictfetchall()
            data = []
            price_unit_dict = {}
            for rec in query_result:
                name = rec.get('product_name') or ''
                if not name or '委外' in name:
                    continue
                typs = rec.get('type')
                quantity = abs(rec.get('quantity'))
                bs_type = rec.get('bs_type') or ''
                if bs_type == '采购退货':
                    quantity = -quantity
                    typs = 'in'
                if bs_type == '物料申领退回':
                    quantity = -quantity
                    typs = 'out'
                if rec.get('date'):
                    date = (rec.get('date') + timedelta(hours=7)).strftime('%Y-%m-%d %H:%M:%S')
                else:
                    date = ''
                picking_id = stock_picking.search([('id', '=', rec.get('picking_id'))])
                department = picking_id.apply_id.department_id.display_name
                apply = picking_id.work_station_id.display_name or department
                if not department and bs_type == '生产领料':
                    department = '制造部'
                stock_move_id = stock_move.search([('id', '=', rec.get('move_id'))])
                purchase_line = stock_move_id.purchase_line_id
                key = f"{rec.get('default_code')}-{rec.get('lot_id')}"
                if key in price_unit_dict:
                    p, currency_name, purchase_ids, uom_po_id = self.get_price_unit(rec)
                    price_unit = price_unit_dict[key] if p else p
                else:
                    price_unit, currency_name, purchase_ids, uom_po_id = self.get_price_unit(rec)
                    price_unit_dict[key] = price_unit
                uom_id = uom.search([('id', '=', rec.get('uom_id'))])
                product_id = stock_move_id.product_id
                # 移动数量转库存数量
                moveline_id = stock_move_line.browse(rec.get('move_line_id'))

                if uom_id != product_id.uom_id and moveline_id.quantity_product_uom:
                    factor = round(quantity, 4) / round(moveline_id.quantity_product_uom, 4)
                    if product_id.uom_id.factor != round(factor, 4):
                        quantity = quantity / factor
                        po_uom_qty = quantity * uom_po_id.factor
                        price = price_unit * factor
                    else:
                        quantity = uom_id._compute_quantity(quantity, product_id.uom_id)  # 移动单位转换库存单位数量
                        po_uom_qty = product_id.uom_id._compute_quantity(quantity, uom_po_id, round=False)
                        price = uom_po_id._compute_price(price_unit, product_id.uom_id)
                else:
                    quantity = uom_id._compute_quantity(quantity, product_id.uom_id)  # 移动单位转换库存单位数量
                    po_uom_qty = product_id.uom_id._compute_quantity(quantity, uom_po_id, round=False)
                    price = uom_po_id._compute_price(price_unit, product_id.uom_id)
                subtotal = quantity * price
                supplier_ids = purchase_ids.partner_id
                dtl = {
                    'move_line_id': rec.get('move_line_id') or '',
                    'reference': rec.get('reference') or '',
                    'product_name': rec.get('product_name') or '',
                    'po_uom_name': product_id.uom_po_id.name or '',
                    'po_uom_qty': po_uom_qty,
                    'default_code': rec.get('default_code') or '',
                    'date': date,
                    'location_name': rec.get('location_name') or '',
                    'location_dest_name': rec.get('location_dest_name') or '',
                    'lot_name': rec.get('lot_name') or '',
                    'quantity': quantity,
                    'uom_name': product_id.uom_id.name,
                    'price_unit': price_unit,
                    'purchase': ','.join([i.name or '' for i in purchase_ids]),
                    'supplier': ','.join([i.name or '' for i in supplier_ids]),
                    'supplier_code': ','.join([i.code or '' for i in supplier_ids]),
                    'supplier_en': ','.join([i.name_en or '' for i in supplier_ids]),
                    'apply': apply,
                    'department': department,
                    'type': typs,
                    'bs_type': bs_type,
                    'currency_name': currency_name,
                    'price': price,
                    'price_total': subtotal,
                    'subtotal': subtotal,
                    'user_id': ','.join([i.name or '' for i in purchase_ids.create_uid]),
                    'country_id': ','.join([i.name or '' for i in supplier_ids.country_id]),
                    'tax': ','.join([x.name or '' for x in purchase_line.taxes_id])
                }
                data.append(dtl)
            sheet1 = self.get_sheet(book.add_sheet('入库明细'), header_field_style)
            sheet2 = self.get_sheet(book.add_sheet('出库明细'), header_field_style, t='out')
            index_in = 1
            index_out = 1
            for i, item in enumerate(data):
                item_list = [
                    item.get('move_line_id') or '',
                    item.get('purchase') or '',
                    item.get('supplier') or '',
                    item.get('supplier_code') or '',
                    item.get('supplier_en') or '',
                    item.get('reference') or '',
                    item.get('apply') or '',
                    item.get('product_name') or '',
                    item.get('default_code') or '',
                    item.get('department') or '',
                    item.get('bs_type') or '',
                    item.get('date') or '',
                    item.get('location_name') or '',
                    item.get('location_dest_name') or '',
                    item.get('lot_name') or '',
                    item.get('po_uom_qty') or 0,  # 采购数量
                    item.get('po_uom_name') or '',  # 采购单位
                    item.get('price_unit') or 0,  # 采购单价
                    item.get('price_total') or 0,  # 采购总额
                    -item.get('po_uom_qty') if item.get('quantity') < 0 else item.get('po_uom_qty'),  # (出/入)库数
                    item.get('po_uom_name') or '',  # (出/入)库单位
                    item.get('quantity') or 0,
                    item.get('uom_name') or '',  # 库存单位
                    item.get('price') or 0,
                    item.get('subtotal') or 0,
                    item.get('currency_name') or '',
                    item.get('user_id') or '',
                    item.get('country_id') or '',
                    item.get('tax') or '',
                ]
                i += 1
                if item.get('type') == 'out':
                    sheet2.row(index_out).height_mismatch = True
                    sheet2.row(index_out).height = 300
                    for index, v in enumerate(item_list):
                        sheet2.write(index_out, index, v, field_style)
                    index_out += 1
                else:
                    sheet1.row(index_in).height_mismatch = True
                    sheet1.row(index_in).height = 300
                    for index, v in enumerate(item_list):
                        sheet1.write(index_in, index, v, field_style)
                    index_in += 1
            from_date = datetime.fromisoformat(from_date)
            to_date = datetime.fromisoformat(to_date)
            date1 = (from_date + timedelta(hours=7)).strftime('%Y-%m-%d')
            date2 = (to_date + timedelta(hours=7)).strftime('%Y-%m-%d')
            file_name = f'物料收支 {date1}  -  {date2}.xls'
        elif sb_type == 'balance':
            lang = request.env.context.get('lang')
            init_all_query = f"""
                with 
                location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
                location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
                location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
                cte as (
                    select 
                        sml.product_id,
                        pt.name  pt_name,
                        pp.default_code,
                        pc.name  pc_name,
                        sum(sml.quantity) as quantity,
                        sum(sml.quantity_product_uom) as quantity_product_uom,
                        uu2.id  uu2_id,
                        sml.lot_id,
                        lot.name          as lot_name
                    from stock_move_line sml
                        left join product_product pp on sml.product_id = pp.id
                        left join product_template pt on pp.product_tmpl_id = pt.id
                        left join product_category pc on pt.categ_id = pc.id
                        left join uom_uom uu2 on sml.product_uom_id = uu2.id
                        left join stock_lot lot on sml.lot_id = lot.id
                    where sml.state = 'done' AND
                        sml.date < '{to_date}' AND
                        sml.location_dest_id in (SELECT id FROM location3) AND
                        sml.location_id not in (SELECT id FROM location3) AND
                        pc.id not in {tuple(category_ids)}
                    group by sml.product_id, pt.name, pp.default_code, pc.name, uu2.id,sml.lot_id, lot.name
                    union all
                    select 
                        sml.product_id,
                        pt.name pt_name,
                        pp.default_code,
                        pc.name pc_name,
                        -sum(sml.quantity) as quantity,
                        -sum(sml.quantity_product_uom) as quantity_product_uom,
                        uu2.id  uu2_id, 
                        sml.lot_id,
                        lot.name          as lot_name
                    from stock_move_line sml
                        left join stock_move sm on sml.move_id = sm.id
                        left join product_product pp on sml.product_id = pp.id
                        left join product_template pt on pp.product_tmpl_id = pt.id
                        left join product_category pc on pt.categ_id = pc.id
                        left join uom_uom uu2 on sml.product_uom_id = uu2.id
                        left join stock_lot lot on sml.lot_id = lot.id
                    where sml.state = 'done' AND
                        sml.date < '{to_date}' AND
                        sml.location_id in (SELECT id FROM location3) AND
                        sml.location_dest_id not in (SELECT id FROM location3) AND
                        pc.id not in {tuple(category_ids)}
                    group by sml.product_id, pt.name, pp.default_code, pc.name, uu2.id, sml.lot_id, lot.name
                )
                select 
                    product_id,
                    pt_name as name,
                    default_code,
                    pc_name category,
                    round(sum(quantity), 3) as quantity,
                    sum(quantity_product_uom) as quantity_product_uom,
                    uom.name ->> '{lang}' as uom,
                    uom.id as uom_id,
                    cte.lot_id,
                    lot_name
                from cte
                    left join uom_uom uom on cte.uu2_id = uom.id
    
                group by product_id, pt_name, default_code, pc_name, uom.id, uom.name,cte.lot_id,lot_name;
            """
            request.env.cr.execute(init_all_query)
            result = request.env.cr.dictfetchall()
            # 数据汇总,通过 产品-货币-批次 分组
            currency_id_defaultdict = defaultdict(list)
            price_unit_dict = {}
            for value in result:
                name = value['name']
                if not name or '委外' in name:
                    continue
                product_id = request.env['product.product'].browse(value['product_id'])
                if product_id.detailed_type == 'consu':
                    continue
                value['po_uom_name'] = product_id.uom_po_id.name
                if value['lot_id']:
                    lot_id = stock_lot.search([('id', '=', value['lot_id'])])
                else:
                    lot_id = stock_lot
                purchase = lot_id.purchase_order_ids
                purchase_line = purchase.order_line
                line_ids = purchase_line.filtered(
                    lambda x: x.product_id == lot_id.product_id and x.price_unit > 0 and x.product_uom_qty > 0)
                if not line_ids and product_id.id in [9465, 11314, 262]:
                    purchase_line = request.env['purchase.order.line'].search([('product_id', '=', product_id.id)])
                    purchase = purchase_line.order_id
                    line_ids = purchase_line
                uom = request.env['uom.uom'].browse(value.get('uom_id'))  # 移动单位
                value['uom'] = product_id.uom_id.name  # 库存单位
                if uom != product_id.uom_id:
                    value['quantity'] = value['quantity_product_uom']  # 移动单位转换库存单位数量
                # 采购单价转库存单价
                if len(line_ids) > 1:
                    unit_list = set([i.price_unit for i in line_ids])
                    if len(unit_list) > 1:  # 存在差异, 计算入库总额, 出库总额
                        value['flag'] = True
                        price_unit = 0
                    else:
                        price_unit = self.get_uom_price_unit(line_ids[0], product_id)
                elif line_ids:
                    price_unit = self.get_uom_price_unit(line_ids, product_id)
                else:
                    price_unit = line_ids.price_unit
                uom_po_id = purchase_line[0].product_uom if purchase_line else product_id.uom_po_id
                # 供应商
                supplier = list(set(purchase.mapped('partner_id.name')))
                lot_name = list(set([i or '' for i in lot_id.mapped('name') if i]))
                try:
                    po_uom_qty = product_id.uom_id._compute_quantity(value['quantity'], uom_po_id)
                except Exception as e:
                    po_uom_qty = product_id.uom_id._compute_quantity(value['quantity'], product_id.uom_po_id)
                value['po_uom_qty'] = po_uom_qty  # 采购数量
                value['supplier'] = supplier[0] if supplier else ''
                value['price_unit'] = price_unit
                value['lot_name'] = ','.join(lot_name)
                value['currency_id'] = purchase.currency_id[0].name if purchase.currency_id else ''
                currency_id_defaultdict[f"{value['product_id']}-{purchase.currency_id}-{value['lot_id']}"].append(value)
            product_result = []
            for k, values in currency_id_defaultdict.items():
                product_id, currency_id, lot_id = k.split('-')
                # 根据批次合并数量
                flag = any([i.get('flag') for i in values])
                total_quantity = sum([i.get('quantity') for i in values])
                if total_quantity <= 0.0001:
                    continue
                lot_qty_dict = {}
                for v in values:
                    if not v.get('lot_id'):
                        continue
                    if v.get('lot_id') not in lot_qty_dict:
                        lot_qty_dict[v.get('lot_id')] = v.get('quantity')
                    else:
                        lot_qty_dict[v.get('lot_id')] += v.get('quantity')
                lot_ids = []
                for ke, v in lot_qty_dict.items():
                    if v <= 0:
                        continue
                    if ke and ke not in lot_ids:
                        lot_ids.append(ke)
                vl = [i for i in values if i.get('lot_id') in lot_ids]
                if not vl:
                    dtl = values[0]
                else:
                    dtl = vl[0]
                dtl['lot_name'] = ','.join(list(set([i.get('lot_name') for i in vl])))
                dtl['supplier'] = ','.join(list(set([i.get('supplier') for i in values])))
                if flag and lot_ids:
                    if 6719 in lot_ids:
                        price_unit = 20.2765097690941
                        sub_total = price_unit * total_quantity
                    else:
                        querys = self.get_query(str(datetime(year=1900, month=1, day=1)), to_date, category_ids,
                                                dtl['default_code'], lot_ids)
                        price_unit, sub_total, price_unit_dict = self.get_lot_price_unit(querys, price_unit_dict)
                else:
                    # 根据批次汇总出入库
                    lot_defaultdict = defaultdict(list)
                    for rec in values:
                        lot_defaultdict[rec['lot_id']].append(rec)
                    lot_len_isd = []
                    for kes, items in lot_defaultdict.items():
                        quant = sum([i.get('quantity') for i in items])
                        if quant > 0:
                            lot_len_isd.append(kes)
                    lln = list(set(lot_len_isd))  # 差异批次
                    if len(lln) > 1:
                        querys = self.get_query(str(datetime(year=1900, month=1, day=1)), to_date, category_ids,
                                                dtl['default_code'], lot_ids)
                        price_unit, sub_total, price_unit_dict = self.get_lot_price_unit(querys, price_unit_dict)
                    else:
                        total = 0
                        qty = 0
                        for rec in values:
                            total += rec['price_unit'] * rec['quantity']
                            qty += rec['quantity']
                        price_unit = total / qty
                        sub_total = price_unit * total_quantity
                        if int(product_id) == 11314:
                            price_unit = 20.28203712
                            sub_total = price_unit * total_quantity
                dtl['quantity'] = total_quantity
                dtl['price_unit'] = price_unit
                dtl['po_uom_qty'] = sum([i.get('po_uom_qty', 0) for i in values])
                dtl['subtotal'] = sub_total
                product_result.append(dtl)
            sheet3 = book.add_sheet('期末结存')
            sheet3.write(0, 0, '产品', header_field_style)
            sheet3.write(0, 1, '编码', header_field_style)
            sheet3.write(0, 2, '分类', header_field_style)
            sheet3.write(0, 3, '批次', header_field_style)
            sheet3.write(0, 4, '币种', header_field_style)
            sheet3.write(0, 5, '供应商', header_field_style)
            sheet3.write(0, 6, '库存数量1', header_field_style)
            sheet3.write(0, 7, '库存单位1', header_field_style)
            sheet3.write(0, 8, '库存数量2', header_field_style)
            sheet3.write(0, 9, '库存单位2', header_field_style)
            sheet3.write(0, 10, '单价', header_field_style)
            sheet3.write(0, 11, '总价', header_field_style)
            sheet3.col(0).width = 256 * 84
            sheet3.col(1).width = 256 * 48
            sheet3.col(2).width = 256 * 12
            sheet3.col(3).width = 256 * 30
            sheet3.col(4).width = 256 * 10
            sheet3.col(5).width = 256 * 50
            sheet3.col(6).width = 256 * 25
            sheet3.col(7).width = 256 * 15
            sheet3.col(8).width = 256 * 15
            sheet3.col(9).width = 256 * 15
            sheet3.col(10).width = 256 * 15
            sheet3.col(11).width = 256 * 15
            for i, item in enumerate(product_result):
                i += 1
                sheet3.row(i).height_mismatch = True
                sheet3.row(i).height = 300
                item_list = [
                    item.get('name') or '',
                    item.get('default_code') or '',
                    item.get('category') or '',
                    item.get('lot_name') or '',
                    item.get('currency_id') or '',
                    item.get('supplier') or '',
                    item.get('po_uom_qty') or 0,
                    item.get('po_uom_name') or '',
                    item.get('quantity') or 0,
                    item.get('uom') or '',
                    item.get('price_unit') or 0,
                    item.get('subtotal') or 0
                ]
                for index, v in enumerate(item_list):
                    sheet3.write(i, index, v, field_style)
            file_name = f'物料结存 {(datetime.fromisoformat(to_date) + timedelta(hours=7)).strftime("%Y-%m-%d")}.xls'
        else:
            return ''
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response

    def get_price_unit(self, rec):
        stock_lot = request.env['stock.lot'].sudo()
        stock_move = request.env['stock.move'].sudo()
        stock_move_id = stock_move.search([('id', '=', rec.get('move_id'))])
        product_id = stock_move_id.product_id
        purchase_line = stock_move_id.purchase_line_id
        purchase_ids = purchase_line.order_id
        price_unit = purchase_line.price_unit
        currency_name = purchase_line.currency_id.name
        if not purchase_ids and rec.get('lot_id'):
            lot_id = stock_lot.search([('id', '=', rec.get('lot_id'))])
            purchase_ids = lot_id.purchase_order_ids
            purchase_line = purchase_ids.order_line.filtered(lambda x: x.product_id.id == rec.get('product_id'))
            line_ids = purchase_line.filtered(lambda x: x.price_unit > 0 and x.product_uom_qty > 0)
            if len(line_ids) > 1:
                # 判断单价是否一致
                unit_list = set([i.price_unit for i in line_ids])
                if len(unit_list) > 1:
                    if 6719 == lot_id.id:
                        price_unit = 4566.28
                    else:
                        # 对单价进行加权平均
                        total = 0
                        for line in line_ids:
                            total += line.price_unit * line.product_uom_qty
                        price_unit = total / sum(line_ids.mapped('product_uom_qty'))
                    currency_name = line_ids[0].currency_id.name
                else:
                    price_unit = line_ids[0].price_unit
                    currency_name = line_ids[0].currency_id.name
            elif len(line_ids) == 1:
                price_unit = line_ids.price_unit
                currency_name = purchase_ids[0].currency_id.name if purchase_ids else ''
        if rec.get('move_line_id') == 9346:
            purchase_ids, currency_name = self.get_po('WTTP20250324008')
            price_unit = 20.28203712
        if rec.get('move_line_id') == 12482:
            purchase_ids, currency_name = self.get_po('WTTP20250404032')
            price_unit = 20.28203712
        if rec.get('move_line_id') in [40410, 15870, 23667]:
            purchase_ids, currency_name = self.get_po('WTTP20250408008')
            price_unit = 20.123
        uom_po_id = purchase_line[0].product_uom if purchase_line else product_id.uom_po_id
        return price_unit, currency_name, purchase_ids, uom_po_id

    def get_po(self, name):
        purchase_ids = request.env['purchase.order'].search([('name', '=', name)], limit=1)
        currency_name = purchase_ids.currency_id.name if purchase_ids else ''
        return purchase_ids, currency_name

    def get_uom_price_unit(self, line_ids, product_id):
        if line_ids.product_uom != product_id.uom_id:
            # 获取单位比例
            round_raw = product_id.uom_id.factor
            product_uom_qty = round_raw * line_ids.product_qty
            if product_uom_qty != line_ids.product_uom_qty:
                round_f = line_ids.product_qty / line_ids.product_uom_qty
                price_unit = line_ids.price_unit * round_f
            else:
                price_unit = line_ids.product_uom._compute_price(line_ids.price_unit, product_id.uom_id)
        else:
            price_unit = line_ids.product_uom._compute_price(line_ids.price_unit, product_id.uom_id)
        return price_unit

    def get_lot_price_unit(self, querys, price_unit_dict):
        stock_move = request.env['stock.move'].sudo()
        uom = request.env['uom.uom'].sudo()

        request.env.cr.execute(querys)
        querys_result = request.env.cr.dictfetchall()
        datas = []
        for rec in querys_result:
            name = rec.get('product_name') or ''
            if not name or '委外' in name:
                continue
            typs = rec.get('type')
            quantity = abs(rec.get('quantity'))
            bs_type = rec.get('bs_type') or ''
            if bs_type == '采购退货':
                quantity = -quantity
                typs = 'in'
            if bs_type == '物料申领退回':
                quantity = -quantity
                typs = 'out'
            key = f"{rec.get('default_code')}-{rec.get('lot_id')}"
            if key in price_unit_dict:
                _, currency_name, purchase_ids, uom_po_id = self.get_price_unit(rec)
                price_unit = price_unit_dict[key]
            else:
                price_unit, currency_name, purchase_ids, uom_po_id = self.get_price_unit(rec)
                price_unit_dict[key] = price_unit
            stock_move_id = stock_move.search([('id', '=', rec.get('move_id'))])
            product_id = stock_move_id.product_id
            price = uom_po_id._compute_price(price_unit, product_id.uom_id)
            uom_id = uom.search([('id', '=', rec.get('uom_id'))])
            # 移动数量转库存数量
            if uom_id != product_id.uom_id:
                quantity = uom_id._compute_quantity(quantity, product_id.uom_id)  # 移动单位转换库存单位数量
            result_dtl = {
                'quantity': quantity,
                'price_total': quantity * price,
                'type': typs,
            }
            datas.append(result_dtl)
        in_total = sum([i.get('price_total') if i.get('type') == 'in' else 0 for i in datas])
        out_total = sum([i.get('price_total') if i.get('type') == 'out' else 0 for i in datas])
        sub_total = in_total - out_total
        in_qty = sum([i.get('quantity') if i.get('type') == 'in' else 0 for i in datas])
        out_qty = sum([i.get('quantity') if i.get('type') == 'out' else 0 for i in datas])
        sub_qty = in_qty - out_qty
        price_unit = sub_total / sub_qty if sub_qty > 0 else 0
        return price_unit, sub_total, price_unit_dict

    def get_sheet(self, sheet, header_field_style, t='in'):
        sheet.write(0, 0, '唯一值', header_field_style)
        sheet.write(0, 1, '采购订单', header_field_style)
        sheet.write(0, 2, '供应商', header_field_style)
        sheet.write(0, 3, '供应商编码', header_field_style)
        sheet.write(0, 4, '供应商英文名', header_field_style)
        sheet.write(0, 5, '调拨单', header_field_style)
        sheet.write(0, 6, '领用工序', header_field_style)
        sheet.write(0, 7, '产品', header_field_style)
        sheet.write(0, 8, '编码', header_field_style)
        sheet.write(0, 9, '领用部门', header_field_style)
        sheet.write(0, 10, '物料申领类型', header_field_style)
        sheet.write(0, 11, '日期', header_field_style)
        sheet.write(0, 12, '源位置', header_field_style)
        sheet.write(0, 13, '目的位置', header_field_style)
        sheet.write(0, 14, '批次/序列号码', header_field_style)
        sheet.write(0, 15, '采购数量', header_field_style)
        sheet.write(0, 16, '采购单位', header_field_style)
        sheet.write(0, 17, '采购单价', header_field_style)
        sheet.write(0, 18, '采购总价', header_field_style)
        if t == 'in':
            sheet.write(0, 19, '入库数量', header_field_style)
            sheet.write(0, 20, '入库单位1', header_field_style)
            sheet.write(0, 21, '入库数量', header_field_style)
            sheet.write(0, 22, '入库单位2', header_field_style)
        else:
            sheet.write(0, 19, '出库数量', header_field_style)
            sheet.write(0, 20, '出库单位1', header_field_style)
            sheet.write(0, 21, '出库数量', header_field_style)
            sheet.write(0, 22, '出库单位2', header_field_style)
        sheet.write(0, 23, '库存单价(单位2)', header_field_style)
        sheet.write(0, 24, '库存总额', header_field_style)
        sheet.write(0, 25, '币种', header_field_style)
        sheet.write(0, 26, '采购员', header_field_style)
        sheet.write(0, 27, '国家', header_field_style)
        sheet.write(0, 28, '税率', header_field_style)
        sheet.col(0).width = 256 * 22
        sheet.col(1).width = 256 * 22
        sheet.col(2).width = 256 * 56
        sheet.col(3).width = 256 * 15
        sheet.col(4).width = 256 * 56
        sheet.col(5).width = 256 * 25
        sheet.col(6).width = 256 * 20
        sheet.col(7).width = 256 * 85
        sheet.col(8).width = 256 * 49
        sheet.col(9).width = 256 * 22
        sheet.col(10).width = 256 * 25
        sheet.col(11).width = 256 * 22
        sheet.col(12).width = 256 * 15
        sheet.col(13).width = 256 * 15
        sheet.col(14).width = 256 * 38
        sheet.col(15).width = 256 * 15
        sheet.col(16).width = 256 * 20
        sheet.col(17).width = 256 * 20
        sheet.col(18).width = 256 * 20
        sheet.col(19).width = 256 * 20
        sheet.col(20).width = 256 * 20
        sheet.col(21).width = 256 * 20
        sheet.col(22).width = 256 * 20
        sheet.col(23).width = 256 * 20
        sheet.col(24).width = 256 * 20
        sheet.col(25).width = 256 * 15
        sheet.col(26).width = 256 * 15
        sheet.col(27).width = 256 * 15
        sheet.col(28).width = 256 * 15
        return sheet

    @http.route('/download/sb/finished/quant/excel', type='http', methods=['get'])
    def finished_post(self, **kwargs):
        from_date = datetime.fromisoformat(kwargs.get("from_date"))
        to_date = datetime.fromisoformat(kwargs.get("to_date"))
        book = xlwt.Workbook(encoding='utf-8')
        header_field_style = self.create_style(15 * 15, bold=True)
        field_style = self.create_style(15 * 15)
        c = request.env['product.category'].sudo().search([('code', '=', 'OPFP')])
        c |= c.child_id
        category_ids = [request.env.ref('sb_sale.finished_product_type').id]
        category_ids.extend(c.ids)
        stock_move = request.env['stock.move'].sudo()
        stock_move_line = request.env['stock.move.line'].sudo()
        production = request.env['mrp.production'].sudo()
        setl = request.env.ref('pcbmanufact.uom_set_length')
        setw = request.env.ref('pcbmanufact.uom_set_width')
        sheet = request.env.ref('pcbmanufact.uom_set')
        sb_type = kwargs.get("tp")
        if sb_type == 'detail':
            query = f"""
        WITH
            -- 获取库存位置
            location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
            location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
            location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
            -- 生产前位置
            location4 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal')),
            location5 AS (SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal'),
            location6 AS (SELECT l4.id FROM location4 l4 UNION ALL SELECT l5.id FROM location5 l5 ORDER BY id asc),
            product_finish AS (
            SELECT pp.id, pt.name,pc.id as categ_id,pc.name as category_name, pt.default_code
            FROM product_product pp
                LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
                LEFT JOIN product_category pc on pt.categ_id=pc.id
            WHERE pc.id in {'(' + str(category_ids[0]) + ')' if len(category_ids) == 1 else tuple(category_ids)}),
            move_line AS (
            -- 出
                SELECT
                    sml.id,
					sml.reference,
                    sml.product_id,
                    sml.location_id,
                    in_lo.name as location_name,
                    sml.location_dest_id,
                    out_lo.name as location_dest_name,
                    sml.date,
                    sl.name as lot_name,
                    uu.name ->> 'zh_CN' as uom_name,
                    -sml.quantity as quantity,
                    'out' as type,
                    sm.id as move_id,
                    sml.lot_id
                FROM stock_move_line sml
                    LEFT JOIN stock_move sm ON sm.id=sml.move_id
                    LEFT JOIN stock_location in_lo on in_lo.id=sml.location_id
                    LEFT JOIN stock_location out_lo on out_lo.id=sml.location_dest_id
                    LEFT JOIN stock_lot sl on sl.id=sml.lot_id
					LEFT JOIN uom_uom uu on uu.id=sml.product_uom_id
                    LEFT JOIN product_finish pf ON pf.id=sml.product_id
                WHERE
                    sml.state='done' AND
                    sml.date>='{from_date}' AND
                    sml.date<='{to_date}' AND
                    sml.location_id in (SELECT id FROM location3) AND
                    sml.location_dest_id not in (SELECT id FROM location3) AND
                    pf.categ_id in {'(' + str(category_ids[0]) + ')' if len(category_ids) == 1 else tuple(category_ids)}
                UNION ALL
            -- 入
                SELECT
                    sml.id,
					sml.reference,
                    sml.product_id,
                    sml.location_id,
                    in_lo.name as location_name,
                    sml.location_dest_id,
                    out_lo.name as location_dest_name,
                    sml.date,
                    sl.name as lot_name,
                    uu.name ->> 'zh_CN' as uom_name ,
                    sml.quantity,
                    'in' as type,
                    sm.id as move_id,
                    sml.lot_id
                FROM stock_move_line sml
                    LEFT JOIN stock_move sm ON sm.id=sml.move_id
                    LEFT JOIN stock_location in_lo on in_lo.id=sml.location_id
                    LEFT JOIN stock_location out_lo on out_lo.id=sml.location_dest_id
                    LEFT JOIN stock_lot sl on sl.id=sml.lot_id
                    LEFT JOIN uom_uom uu on uu.id=sml.product_uom_id
                    LEFT JOIN product_finish pf ON pf.id=sml.product_id
                WHERE
                    sml.state='done' AND
                    sml.date>='{from_date}' AND
                    sml.date<='{to_date}' AND
                    sml.location_dest_id in (SELECT id FROM location3) AND
                    sml.location_id not in (SELECT id FROM location3) AND
                    pf.categ_id in {'(' + str(category_ids[0]) + ')' if len(category_ids) == 1 else tuple(category_ids)}
            ),
            -- 连表操作取值
            main AS (
                SELECT
                    ml.id,
                    ml.reference,
                    pf.name as product_name,
                    pf.default_code,
                    pf.category_name,
                    ml.location_name,
                    ml.location_dest_name,
                    ml.date,
                    ml.lot_name,
                    ml.uom_name,
                    ml.quantity,
                    ml.type,
                    ml.move_id,
                    ml.product_id,
                    ml.lot_id,
                    CASE
                        --出库
                        WHEN sli.usage='internal' AND slo.usage='supplier' THEN '采购退货'
                        WHEN sli.usage='internal' AND slo.usage='customer' THEN '销售出库'
                        WHEN sli.usage='internal' AND slo.id in (select id from location6) THEN '生产出库'
                        WHEN sli.id=28 AND slo.id=14 THEN '盘亏出库'
                        --入库
                        WHEN sli.usage='supplier' AND slo.usage='internal' THEN '采购入库'
                        WHEN sli.usage='customer' AND slo.usage='internal' THEN '销售退货'
                        WHEN sli.id in (select id from location6) AND slo.usage='internal' THEN '生产入库'
                        WHEN sli.id=14 AND slo.id=28 THEN '盘盈入库'
                    ELSE NULL END AS bs_type
                FROM move_line ml
                    LEFT JOIN product_finish pf ON pf.id=ml.product_id
                    LEFT JOIN stock_location sli ON ml.location_id=sli.id
                    LEFT JOIN stock_location slo ON ml.location_dest_id=slo.id
                WHERE pf.name is NOT NULL
            )
            SELECT * FROM main
        """
            request.env.cr.execute(query)
            query_result = request.env.cr.dictfetchall()
            # 出入库明细航
            data = []
            for rec in query_result:
                move_id = stock_move.search([('id', '=', rec.get('move_id'))])
                product_id = move_id.product_id
                picking_id = move_id.picking_id
                picking_type_id = move_id.picking_type_id
                lot_name = rec.get('lot_name') or ''
                lot_id = request.env['stock.lot'].sudo().search([('id', '=', rec.get('lot_id'))])
                production_id = lot_id.s_production_id
                if picking_type_id.barcode == 'WTT-DELIVERY':
                    order_line = move_id.sale_line_id
                    partner = order_line.order_id.partner_id
                    # production_id = order_line.order_id.mrp_production_ids
                else:
                    move_line_id = stock_move_line.search([('id', '=', rec.get('id'))])
                    # production_id = move_line_id.production_id
                    order_line = production_id.s_sale_order_line_ids
                    partner = order_line.order_id.partner_id
                    if not production_id:
                        order_line = move_id.sale_line_id
                        partner = order_line.order_id.partner_id
                        production_id = order_line.order_id.mrp_production_ids
                    if not production_id:
                        domain = [
                            ('active', 'in', [True, False]),  # 归档也要显示
                            ('product_id', '=', rec.get('product_id')),
                            ('lot_producing_id', '=', rec.get('lot_id'))]
                        production_id = production.search(domain, limit=1)
                        order_line = production_id.s_sale_order_line_ids
                        partner = production_id.customer
                    if not order_line:
                        stock_lot = move_line_id.lot_id
                        order_line = stock_lot.sale_order_ids.order_line.filtered(
                            lambda x: x.product_id.detailed_type == 'product')
                        if not production_id:
                            production_id = order_line.order_id.mrp_production_ids
                        if not order_line:
                            move_line = stock_move_line.search([('lot_id', '=', stock_lot.id), ('state', '=', 'done')])
                            production_ids = move_line.move_id.production_id
                            for i in move_line:
                                if i.move_id.raw_material_production_id and not i.move_id.scrapped:
                                    production_ids |= i.move_id.raw_material_production_id
                            order_line = production_ids.s_sale_order_line_ids
                sale_name = ','.join(list(set(order_line.order_id.mapped('name'))))
                sale_pn = ','.join(list(set(order_line.mapped('pn'))))
                order_line = order_line[0] if order_line else order_line
                if not partner and order_line:
                    partner = order_line[0].order_id.partner_id
                uom_ids = product_id.uom_value_ids
                set_length = uom_ids.filtered(lambda x: x.uom_id.id == setl.id).uom_value
                set_width = uom_ids.filtered(lambda x: x.uom_id.id == setw.id).uom_value
                # SET里的PCS数
                pcs_set = uom_ids.filtered(lambda x: x.uom_id.id == sheet.id).uom_value
                sheet_are = 0
                if set_width and set_length:
                    sheet_are = float(set_length) * float(set_width) / 1000000
                if rec.get('date'):
                    date = (rec.get('date') + timedelta(hours=7)).strftime('%Y-%m-%d %H:%M:%S')
                else:
                    date = ''
                quantity = abs(rec.get('quantity'))
                types = rec.get('type')
                if rec.get('bs_type') in ('生产出库',):
                    types = 'in'
                    quantity = -quantity
                if rec.get('bs_type') in ('销售退货',):
                    types = 'out'
                    quantity = -quantity
                if rec.get('location_dest_name') == '生产前' and picking_id.sb_rework_check_id:
                    picking_type_name = '成品检修'
                    types = 'in'
                    quantity = -abs(quantity)
                else:
                    picking_type_name = picking_type_id.name
                if picking_type_id.barcode == 'WTT-INTERNAL-REWORK':  # 成品检修
                    types = 'in'
                    quantity = -abs(quantity)
                if types == 'out' and picking_type_name == '采购收货':
                    picking_type_name = '销售退货'
                # 单个成品面积 * 数量
                if pcs_set > 0:
                    pcs_set_quantity = quantity / pcs_set
                    pcsA = sheet_are / pcs_set
                else:
                    pcs_set_quantity = 0
                    pcsA = 0
                subtotal = order_line.price_unit * quantity
                # pcs面积 SET长*SET宽/每SET里的PCS
                currency_id = order_line.currency_id
                cny_currency_id = request.env['res.currency'].search([('name', '=', 'CNY')], limit=1)
                cny_rate = currency_id._get_conversion_rate(currency_id, cny_currency_id)

                base_mo = ''
                if len(production_id) > 1:
                    for i in production_id:
                        if i.base_mo in lot_name:
                            base_mo = i.base_mo
                            break
                else:
                    base_mo = production_id.base_mo
                dtl = {
                    'move_id': rec.get('id'),
                    'reference': rec.get('reference'),
                    'picking_type_name': picking_type_name,
                    'production_name': ','.join([i for i in production_id.mapped('name') if i]),
                    'product_name': rec.get('product_name'),
                    'default_code': rec.get('default_code'),
                    'category_name': rec.get('category_name'),
                    'location_name': rec.get('location_name'),
                    'location_dest_name': rec.get('location_dest_name'),
                    'date': date,
                    'lot_name': rec.get('lot_name'),
                    'base_mo': base_mo,
                    'uom_name': rec.get('uom_name'),
                    'quantity': quantity,
                    'type': types,
                    'sale_name': sale_name,
                    'partner_name': partner.name,
                    'partner_code': partner.code,
                    'PN': sale_pn,
                    'user_name': order_line.order_id.user_id.name,
                    'setA': sheet_are * pcs_set_quantity,
                    'pcsA': pcsA,
                    'cny_rate': cny_rate,
                    'cny_subtotal': cny_rate * subtotal
                }
                data.append(dtl)
            sheet1 = self.get_finished_sheet(book.add_sheet('入库明细'), header_field_style)
            sheet2 = self.get_finished_sheet(book.add_sheet('出库明细'), header_field_style)
            index_in = 1
            index_out = 1
            for i, item in enumerate(data):
                item_list = [
                    item.get('move_id') or '',
                    item.get('reference') or '',
                    item.get('picking_type_name') or '',  # --

                    item.get('product_name') or '',
                    item.get('default_code') or '',
                    item.get('category_name') or '',
                    item.get('location_name') or '',
                    item.get('location_dest_name') or '',
                    item.get('date') or '',
                    item.get('lot_name') or '',
                    item.get('production_name') or '',  # --
                    item.get('base_mo') or '',
                    item.get('uom_name') or '',
                    item.get('quantity') or '',
                    item.get('sale_name') or '',  # --
                    item.get('partner_name') or '',  # --
                    item.get('partner_code') or '',  # --
                    item.get('PN') or '',  # --
                    item.get('user_name') or '',  # --
                    item.get('setA') or '',  # --
                    item.get('pcsA') or '',  # --
                    item.get('cny_rate') or '',  # --
                    item.get('cny_subtotal') or '0',  # --
                ]
                i += 1
                if item.get('type') == 'out':
                    sheet2.row(index_out).height_mismatch = True
                    sheet2.row(index_out).height = 300
                    for index, v in enumerate(item_list):
                        sheet2.write(index_out, index, v, field_style)
                    index_out += 1
                else:
                    sheet1.row(index_in).height_mismatch = True
                    sheet1.row(index_in).height = 300
                    for index, v in enumerate(item_list):
                        sheet1.write(index_in, index, v, field_style)
                    index_in += 1
            from_date = datetime.fromisoformat(kwargs.get("from_date"))
            to_date = datetime.fromisoformat(kwargs.get("to_date"))
            date1 = (from_date + timedelta(hours=7)).strftime('%Y-%m-%d')
            date2 = (to_date + timedelta(hours=7)).strftime('%Y-%m-%d')
            file_name = f'成品收支 {date1}  -  {date2}.xls'
        elif sb_type == 'balance':
            lang = request.env.context.get('lang')
            finished_quant_query = f"""
            with 
                location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
                location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
                location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
                cte as (
                select 
                    sml.product_id,
                    pt.name  pt_name,
                    pp.default_code,
                    pc.name  pc_name,
                    sum(sml.quantity) as quantity,
                    uu2.id  uu2_id,
                    lot.id lot_id,
                    lot.name as lot_name
                from stock_move_line sml
                    left join product_product pp on sml.product_id = pp.id
                    left join product_template pt on pp.product_tmpl_id = pt.id
                    left join product_category pc on pt.categ_id = pc.id
                    left join uom_uom uu2 on sml.product_uom_id = uu2.id
                    left join stock_lot lot on sml.lot_id = lot.id
                where sml.state = 'done' AND
                    sml.date < '{to_date}' AND
                    pp.active = TRUE AND
                    sml.location_dest_id in (SELECT id FROM location3) AND
                    sml.location_id not in (SELECT id FROM location3) AND
                    pc.id in {'(' + str(category_ids[0]) + ')' if len(category_ids) == 1 else tuple(category_ids)}
                group by sml.product_id, pt.name, pp.default_code, pc.name, uu2.id,lot.id
                union all
                select 
                    sml.product_id,
                    pt.name pt_name,
                    pp.default_code,
                    pc.name pc_name,
                    -sum(sml.quantity) as quantity,
                    uu2.id  uu2_id, 
                    lot.id lot_id,
                    lot.name as lot_name
                from stock_move_line sml
                    left join stock_move sm on sml.move_id = sm.id
                    left join product_product pp on sml.product_id = pp.id
                    left join product_template pt on pp.product_tmpl_id = pt.id
                    left join product_category pc on pt.categ_id = pc.id
                    left join uom_uom uu2 on sml.product_uom_id = uu2.id
                    left join stock_lot lot on sml.lot_id = lot.id
                where sml.state = 'done' AND
                    sml.date < '{to_date}' AND
                    pp.active = TRUE AND
                    sml.location_id in (SELECT id FROM location3) AND
                    sml.location_dest_id not in (SELECT id FROM location3) AND
                    pc.id in {'(' + str(category_ids[0]) + ')' if len(category_ids) == 1 else tuple(category_ids)}
                group by sml.product_id, pt.name, pp.default_code, pc.name, uu2.id, lot.id
                )
                select 
                    product_id,
                    pt_name as name,
                    default_code,
                    pc_name category,
                    round(sum(quantity), 3) as quantity,
                    uom.name ->> '{lang}' as uom,
                    uom.id as uom_id,
                    cte.lot_id,
                    lot_name
                from cte
                    left join uom_uom uom on cte.uu2_id = uom.id
                group by product_id, pt_name, default_code, pc_name, uom.id, uom.name,cte.lot_id,lot_name;
            """
            request.env.cr.execute(finished_quant_query)
            result = request.env.cr.dictfetchall()
            product_defaultdict = defaultdict(list)
            for move in result:
                lot_id = move.get('lot_id')
                if not lot_id:
                    continue
                product_defaultdict[f"{move.get('product_id')}-{lot_id}"].append(move)
            product_result = []
            for key_lot, values in product_defaultdict.items():
                quantity = sum([value.get('quantity') or 0 for value in values])
                if quantity <= 0:
                    continue
                key, lot_id = key_lot.split('-')
                product_id = request.env['product.product'].browse(int(key))
                stock_lot = request.env['stock.lot'].browse(int(lot_id))
                move_line = stock_move_line.search([('lot_id', '=', stock_lot.id), ('state', '=', 'done')])
                sale_order = stock_lot.sale_order_ids.mapped('name')
                if not sale_order:
                    production_id = move_line.move_id.production_id
                    for i in move_line:
                        if i.move_id.raw_material_production_id and not i.move_id.scrapped:
                            production_id |= i.move_id.raw_material_production_id
                    sale_order = production_id.s_sale_order_line_ids.mapped('order_id.name')
                if not sale_order:
                    production_id = move_line.production_id
                    order_line = production_id.s_sale_order_line_ids
                    sale_order = order_line.order_id.mapped('name')
                    if not production_id:
                        order_line = move_line.move_id.sale_line_id
                        sale_order = order_line.order_id.mapped('name')
                        production_id = order_line.order_id.mrp_production_ids
                    if not production_id:
                        domain = [
                            ('active', 'in', [True, False]),  # 归档也要显示
                            ('product_id', '=', int(key)),
                            ('lot_producing_id', '=', int(lot_id))]
                        production_id = production.search(domain, limit=1)
                        order_line = production_id.s_sale_order_line_ids
                        sale_order = order_line.order_id.mapped('name')

                sheet_length = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setl.id).uom_value
                sheet_width = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == setw.id).uom_value
                pcs_set = product_id.uom_value_ids.filtered(lambda x: x.uom_id.id == sheet.id).uom_value
                sheet_are = 0
                if sheet_length and sheet_width:
                    sheet_are = float(sheet_width) * float(sheet_length) / 1000000
                setA = sheet_are * quantity
                pcsA = sheet_are / pcs_set if pcs_set > 0 else 0
                dtl = values[0]
                dtl['date'] = str(move_line[0].date.date()) if move_line else ''
                dtl['quantity'] = quantity
                dtl['setA'] = setA
                dtl['pcsA'] = pcsA
                set_lot = set([value.get('lot_name') for value in values])
                dtl['lot_name'] = '，'.join(list(set_lot))
                dtl['sale_name'] = '，'.join(list(set(sale_order)))
                product_result.append(dtl)
            sheet3 = book.add_sheet('期末结存')
            sheet3.write(0, 0, '产品', header_field_style)
            sheet3.write(0, 1, '编码', header_field_style)
            sheet3.write(0, 2, '分类', header_field_style)
            sheet3.write(0, 3, '批次', header_field_style)
            sheet3.write(0, 4, '日期', header_field_style)
            sheet3.write(0, 5, 'MO', header_field_style)
            sheet3.write(0, 6, '单位', header_field_style)
            sheet3.write(0, 7, '数量', header_field_style)
            sheet3.write(0, 8, 'SET面积', header_field_style)
            sheet3.write(0, 9, '单位PCS面积', header_field_style)
            sheet3.col(0).width = 256 * 30
            sheet3.col(1).width = 256 * 30
            sheet3.col(2).width = 256 * 15
            sheet3.col(3).width = 256 * 30
            sheet3.col(4).width = 256 * 15
            sheet3.col(5).width = 256 * 15
            sheet3.col(6).width = 256 * 15
            sheet3.col(7).width = 256 * 15
            sheet3.col(8).width = 256 * 15
            sheet3.col(9).width = 256 * 15
            for i, item in enumerate(product_result):
                i += 1
                sheet3.row(i).height_mismatch = True
                sheet3.row(i).height = 300
                item_list = [
                    item.get('name') or '',
                    item.get('default_code') or '',
                    item.get('category') or '',
                    item.get('lot_name') or '',
                    item.get('date') or '',
                    item.get('sale_name') or '',
                    item.get('uom') or '',
                    item.get('quantity') or 0,
                    item.get('setA') or '',
                    item.get('pcsA') or '',
                ]
                for index, v in enumerate(item_list):
                    sheet3.write(i, index, v, field_style)
            file_name = f'成品结存 {(to_date + timedelta(hours=7)).strftime("%Y-%m-%d")}.xls'
        else:
            return ''
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response

    def get_finished_sheet(self, sheet, header_field_style):
        sheet.write(0, 0, '唯一ID', header_field_style)
        sheet.write(0, 1, '调拨单号', header_field_style)
        sheet.write(0, 2, '调拨类型', header_field_style)
        sheet.write(0, 3, '产品名称', header_field_style)
        sheet.write(0, 4, '产品编码', header_field_style)
        sheet.write(0, 5, '产品分类', header_field_style)
        sheet.write(0, 6, '原位置', header_field_style)
        sheet.write(0, 7, '目的位置', header_field_style)
        sheet.write(0, 8, '日期', header_field_style)
        sheet.write(0, 9, '批次号', header_field_style)
        sheet.write(0, 10, '生产单号', header_field_style)
        sheet.write(0, 11, '基本工单号', header_field_style)
        sheet.write(0, 12, '单位', header_field_style)
        sheet.write(0, 13, '数量', header_field_style)
        sheet.write(0, 14, '销售单号', header_field_style)
        sheet.write(0, 15, '客户名称', header_field_style)
        sheet.write(0, 16, '客户编码', header_field_style)
        sheet.write(0, 17, '厂商编号', header_field_style)
        sheet.write(0, 18, '操作账号', header_field_style)
        sheet.write(0, 19, 'SET面积', header_field_style)
        sheet.write(0, 20, '单位PCS面积', header_field_style)
        sheet.write(0, 21, '汇率', header_field_style)
        sheet.write(0, 22, '人民币总价', header_field_style)
        sheet.col(0).width = 256 * 15
        sheet.col(1).width = 256 * 25
        sheet.col(2).width = 256 * 15
        sheet.col(3).width = 256 * 22
        sheet.col(4).width = 256 * 22
        sheet.col(5).width = 256 * 12
        sheet.col(6).width = 256 * 22
        sheet.col(7).width = 256 * 12
        sheet.col(8).width = 256 * 23
        sheet.col(9).width = 256 * 35
        sheet.col(10).width = 256 * 12
        sheet.col(11).width = 256 * 12
        sheet.col(12).width = 256 * 22
        sheet.col(13).width = 256 * 30
        sheet.col(14).width = 256 * 22
        sheet.col(15).width = 256 * 35
        sheet.col(16).width = 256 * 15
        sheet.col(17).width = 256 * 15
        sheet.col(18).width = 256 * 15
        sheet.col(19).width = 256 * 15
        sheet.col(20).width = 256 * 15
        sheet.col(21).width = 256 * 15
        sheet.col(22).width = 256 * 15
        return sheet

    @http.route('/download/report/stock/in/internal/out/excel/<string:business_data>', type='http', auth='user', website=True)
    def download_report_stock_in_internal_out_report_excel(self, business_data=None, **data):
        business_type, start_date, end_date, product_id = business_data.split(',')
        records = request.env['s.report.stock.in.internal.out'].with_context({
            'business_type': business_type,
            'start_date': start_date,
            'end_date': end_date,
            'product_id': product_id
        }).search([])
        book = xlwt.Workbook(encoding='utf-8')

        def create_style(font_height, center=True, borders=True):
            style = xlwt.XFStyle()
            font = xlwt.Font()
            font.height = font_height
            style.font = font
            if center:
                # 垂直居中 水平居中
                vertically_horizontally_centered = xlwt.Alignment()
                vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
                vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER
                style.alignment = vertically_horizontally_centered
            if borders:
                border = xlwt.Borders()
                border.bottom = border.top = border.left = border.right = xlwt.Borders.THIN
                style.borders = border
            return style

        field_style = create_style(15 * 15, center=True)
        user_tz = timezone(request.env.user.tz or 'UTC')

        if business_type == 'finished_in':
            header = [
                '单据编号', '入库数', '面积(㎡)', '单价', '总金额', '币种', '计量单位', '完成日期', '产品类别', '批次/序列号码',
                '源位置', '目的位置'
            ]
            records = records.sorted(lambda x: (x.product_category_id.id, x.currency_id.id))
            for (category, currency), g in groupby(records, key=lambda x: (x.product_category_id, x.currency_id)):
                g_ids = request.env['s.report.stock.in.internal.out'].concat(*g)
                if category.code == 'CHP':
                    sheet_name = f'成品入库({currency.name or "无币种"})'
                elif category.code == 'OPFP':
                    sheet_name = f'外协加工({currency.name  or "无币种"})'
                else:
                    sheet_name = f'{currency.name}(无币种)'
                sheet = book.add_sheet(sheet_name)
                for i, item in enumerate(header):
                    sheet.write(0, i, item, field_style)
                index = 1
                for rec in g_ids:
                    done_date = utc.localize(rec.done_date).astimezone(user_tz)
                    sheet.write(index, 0, rec.picking_id.name or '', field_style)
                    sheet.write(index, 1, rec.product_qty or '', field_style)
                    sheet.write(index, 2, round(rec.area, 3) or '', field_style)
                    sheet.write(index, 3, rec.price or '', field_style)
                    sheet.write(index, 4, rec.price_total or '', field_style)
                    sheet.write(index, 5, rec.currency_id.name or '', field_style)
                    sheet.write(index, 6, rec.uom_id.name or '', field_style)
                    sheet.write(index, 7, done_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT) or '', field_style)
                    sheet.write(index, 8, rec.product_category_id.name or '', field_style)
                    sheet.write(index, 9, rec.lot_id.name or '', field_style)
                    sheet.write(index, 10, rec.location_id.name or '', field_style)
                    sheet.write(index, 11, rec.location_dest_id.name or '', field_style)
                    index += 1

            file_name = f'成品入库.xls'
        else:
            header = [
                '单据编号', '入库数', '单价', '总金额', '币种', '计量单位', '完成日期', '产品类别', '批次/序列号码',
                '源位置', '目的位置'
            ]
            sheet = book.add_sheet('sheet')
            for i, item in enumerate(header):
                sheet.write(0, i, item, field_style)
            index = 1
            for rec in records:
                done_date = utc.localize(rec.done_date).astimezone(user_tz)
                sheet.write(index, 0, rec.picking_id.name or '', field_style)
                sheet.write(index, 1, rec.product_qty or '', field_style)
                sheet.write(index, 2, rec.price or '', field_style)
                sheet.write(index, 3, rec.price_total or '', field_style)
                sheet.write(index, 4, rec.currency_id.name or '', field_style)
                sheet.write(index, 5, rec.uom_id.name or '', field_style)
                sheet.write(index, 6, done_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT) or '', field_style)
                sheet.write(index, 7, rec.product_category_id.name or '', field_style)
                sheet.write(index, 8, rec.lot_id.name or '', field_style)
                sheet.write(index, 9, rec.location_id.name or '', field_style)
                sheet.write(index, 10, rec.location_dest_id.name or '', field_style)
                index += 1
            file_name = f'进销存.xls'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ])
        book.save(response.stream)
        return response
